package com.ruoyi.system.uitl;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;

import java.io.FileNotFoundException;
import java.io.InputStream;
import java.sql.Connection;
import java.text.SimpleDateFormat;
import java.util.*;

public class ImportBasicExcelUtil {
    static ClassPathResource classPathResource = new ClassPathResource("basicTable.xlsx");
    public static final String timeformatter = "yyyy/MM/dd";
    public static final String timeformatter2 = "yyyy-MM-dd";

    public static void start() throws Exception {

    }

    public static List<List<Map<String,String>>> readExcelWithTitle(ClassPathResource classPathResource) throws Exception{
        InputStream is = null;
        Workbook wb = null;
        try {
            is = classPathResource.getInputStream();

            wb = new XSSFWorkbook(is);

            List<List<Map<String,String>>> result = new ArrayList<List<Map<String,String>>>();//对应excel文件

            int sheetSize = wb.getNumberOfSheets();
            for (int i = 0; i < 1; i++) {//遍历sheet页
                Sheet sheet = wb.getSheetAt(i);
                List<Map<String,String> >sheetList = new ArrayList<Map<String,String>>();

                List<String> titles = new ArrayList<String>();//放置所有的标题

                int rowSize = sheet.getLastRowNum() + 1;
                for (int j = 0; j < rowSize; j++) {//遍历行
                    Row row = sheet.getRow(j);
                    if (row == null) {//略过空行
                        continue;
                    }
                    int cellSize = row.getLastCellNum();//行中有多少个单元格，也就是有多少列
                    if (j == 0) {//第一行是标题行
                        for (int k = 0; k < cellSize; k++) {
                            Cell cell = row.getCell(k);
                            titles.add(cell.toString());
                        }
                    } else {//其他行是数据行
                        Map<String,String> rowMap = new HashMap<String,String>();//对应一个数据行
                        for (int k = 0; k < titles.size(); k++) {
                            Cell cell = row.getCell(k);
                            Object valueObject = formGetCellType(cell);
                            String key = titles.get(k);
                            String value = null;
                            if (valueObject != null) {
                                value = valueObject.toString();
                            }
                            rowMap.put(key, value);
                        }
                        sheetList.add(rowMap);
                    }
                }
                result.add(sheetList);
            }

            return result;
        } catch (FileNotFoundException e) {
            throw e;
        } finally {
            if (wb != null) {
                wb.close();
            }
            if (is != null) {
                is.close();
            }
        }
    }

    public static Object formGetCellType(Cell cell){
        Object value=null;
        try{
            if(cell.getCellType()==cell.CELL_TYPE_BLANK){
                value="";
            }else if(cell.getCellType()==cell.CELL_TYPE_BOOLEAN){
                value=cell.getBooleanCellValue();
            }else if(cell.getCellType()==cell.CELL_TYPE_ERROR){
                value="";
            }else if(cell.getCellType()==cell.CELL_TYPE_FORMULA){
                value=cell.getCellFormula();
            }else if(cell.getCellType()==cell.CELL_TYPE_NUMERIC){
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date tempValue = cell.getDateCellValue();
                    SimpleDateFormat simpleFormat = new SimpleDateFormat(timeformatter);
                    value = simpleFormat.format(tempValue);
                }else {
                    value = String.valueOf(cell.getNumericCellValue());
                }
            }else if(cell.getCellType()==cell.CELL_TYPE_STRING){
                value=cell.getStringCellValue();
            }else{
                value=cell.getDateCellValue();
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        return value;
    }

}
